﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Dapper;
using Entity;
using System.Data;
using IRealize;
using Realize;

namespace WebApplication9.Controllers.DDGL
{
    public class DDSelectController : Controller
    {
        private readonly IConfiguration configuration;

        public IActionResult Index()
        {
            return View();
        }

        public DDSelectController(IConfiguration configuration)
        {
            this.configuration = configuration;
        }


        // 获取级联数据（I级→II级→III级）
        //[HttpGet("FirstLevelnewCX")]
        public async Task<IActionResult> FirstLevelnewCX()
        {

            using (var connection = new SqlConnection(configuration["SqlServer"]))
            {
                await connection.OpenAsync();

                // 查询所有I、II、III级机构的唯一数据
                var allOrgs = await connection.QueryAsync<dynamic>(
                    "SELECT DISTINCT " +
                    "first_kind_id AS id, first_kind_name AS name, 1 AS level " +
                    "FROM human_file_dig " +
                    "WHERE first_kind_id IS NOT NULL AND first_kind_name IS NOT NULL " +
                    "UNION ALL " +
                    "SELECT DISTINCT " +
                    "second_kind_id AS id, second_kind_name AS name, 2 AS level " +
                    "FROM human_file_dig " +
                    "WHERE second_kind_id IS NOT NULL AND second_kind_name IS NOT NULL " +
                    "UNION ALL " +
                    "SELECT DISTINCT " +
                    "third_kind_id AS id, third_kind_name AS name, 3 AS level " +
                    "FROM human_file_dig " +
                    "WHERE third_kind_id IS NOT NULL AND third_kind_name IS NOT NULL");

                // 构建级联数据结构
                var result = new List<dynamic>();

                // 筛选出I级机构(level=1)
                var firstLevelOrgs = allOrgs.Where(o => o.level == 1).DistinctBy(o => o.id).ToList();

                foreach (var firstLevelOrg in firstLevelOrgs)
                {
                    // 筛选出当前I级机构对应的II级机构(通过first_kind_id关联)
                    var secondLevelOrgs = allOrgs
                        .Where(o => o.level == 2 && o.id == firstLevelOrg.id) // II级机构ID与I级机构ID相同
                        .DistinctBy(o => o.id)
                        .ToList();

                    var secondList = new List<dynamic>();

                    foreach (var secondLevelOrg in secondLevelOrgs)
                    {
                        // 筛选出当前II级机构对应的III级机构(通过second_kind_id关联)
                        var thirdLevelOrgs = allOrgs
                            .Where(o => o.level == 3 && o.id == secondLevelOrg.id) // III级机构ID与II级机构ID相同
                            .DistinctBy(o => o.id)
                            .ToList();

                        secondList.Add(new
                        {
                            id = secondLevelOrg.id,
                            name = secondLevelOrg.name,
                            children = thirdLevelOrgs.Select(o => new { id = o.id, name = o.name }).ToList()
                        });
                    }

                    result.Add(new
                    {
                        id = firstLevelOrg.id,
                        name = firstLevelOrg.name,
                        children = secondList.Any() ? secondList : new List<dynamic>()
                    });
                }

                return Ok(result);
            }


        }


        //[HttpGet("JobCategorynewCX")]
        public async Task<IActionResult> JobCategorynewCX()
        {
            using (var connection = new SqlConnection(configuration["SqlServer"]))
            {
                await connection.OpenAsync();

                // 1. 查询所有唯一的职位分类（第1级）
                var jobCategories = await connection.QueryAsync<dynamic>(
                    @"SELECT DISTINCT 
                human_major_kind_id AS id, 
                human_major_kind_name AS name 
              FROM human_file_dig 
              WHERE human_major_kind_id IS NOT NULL 
                AND human_major_kind_name IS NOT NULL");

                // 构建级联数据结构
                var result = new List<dynamic>();

                // 2. 遍历职位分类，查询对应的职位（第2级）
                foreach (var category in jobCategories)
                {
                    // 查询该职位分类下的所有职位
                    var jobs = await connection.QueryAsync<dynamic>(
                        @"SELECT DISTINCT 
                    human_major_id AS id, 
                    hunma_major_name AS name 
                  FROM human_file_dig 
                  WHERE human_major_kind_id = @CategoryId 
                    AND human_major_id IS NOT NULL 
                    AND hunma_major_name IS NOT NULL",
                        new { CategoryId = category.id });

                    // 确保children为非空数组
                    var jobList = jobs.ToList();

                    // 添加到结果集
                    result.Add(new
                    {
                        id = category.id,
                        name = category.name,
                        children = jobList.Any() ? jobList : new List<dynamic>() // 空数组处理
                    });
                }

                // 3. 返回级联数据
                return Ok(result);
            }



        }


        public IActionResult CXYM()
        {
            return View();
        }


        public IActionResult Index1(int pageSize, int currentPage)
        {

            string sqlserver = configuration["SqlServer"];
            using (SqlConnection conn = new SqlConnection(sqlserver))
            {
                //@pageSize, @tableName, @keyName, @currentPage, @rows
                //分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", pageSize);
                param.Add("@keyName", "mch_id");
                param.Add("@tableName", "major_change");
                param.Add("@currentPage", currentPage);
                param.Add("@totalRows", direction: ParameterDirection.Output, dbType: DbType.Int32);
                string sql = "exec [dbo].[proc_FenYedd] @pageSize, @keyName, @tableName, @currentPage, @totalRows out";
                List<Major_Change> list = conn.Query<Major_Change>(sql, param).ToList();

                FenYe<Major_Change> fenYecs = new FenYe<Major_Change>();
                fenYecs.List = list;
                fenYecs.Rows = param.Get<int>("totalRows");
                //return fenYecs;
                return Json(fenYecs);

            }


        }


        public async Task<IActionResult> Search([FromBody] Major_Change request)
        {

            IDDGLD dDGLD = new DDGLD();


            // 调用服务层执行查询
            var result = await dDGLD.CXMajor_name(
               configuration["SqlServer"],
                request.first_kind_name,
                request.second_kind_name,
                request.third_kind_name,
                request.major_kind_name,
                request.major_name,
                request.regist_time,
                request.check_time,
                request.PageNumber,
                request.PageSize

            );


            // 返回分页结果
            return Ok(new
            {
                TotalCount = result.TotalCount,
                Items = result.Items,
                PageNumber = result.PageNumber,
                PageSize = result.PageSize
            });

          


        }

        public IActionResult CXXS()
        {
            return View();
        }


        public IActionResult CXXSSelect(int mch_id)
        {
            return View();
        }

        public IActionResult CXXSSelectQC(int mch_id)
        {
            return View();
        }



        public async Task<IActionResult> CXDD(int id)
        {
            IDDGLD dDGLD = new DDGLD();
            Major_Change fileDig = await dDGLD.SelectHuFiAsync(id, configuration["SqlServer"]);


            // 映射数据为前端需要的格式，包含原机构和原职位ID
            var result = new
            {
                success = true,
                data = new
                {


                    human_id = fileDig.human_id,
                    human_name = fileDig.human_name,
                    // 原机构信息
                    original_first_kind_id = fileDig.first_kind_id,
                    original_first_kind_name = fileDig.first_kind_name,
                    original_second_kind_id = fileDig.second_kind_id,
                    original_second_kind_name = fileDig.second_kind_name,
                    original_third_kind_id = fileDig.third_kind_id,
                    original_third_kind_name = fileDig.third_kind_name,
                    // 原职位信息
                    original_job_category_id = fileDig.major_kind_id,
                    original_job_category_name = fileDig.major_kind_name,
                    original_job_id = fileDig.major_id,
                    original_job_name = fileDig.major_name,
                    // 原薪酬信息
                    original_salary_standard_name = fileDig.salary_standard_name,
                    original_salary_total = fileDig.salary_sum,


                    // 新机构信息
                    new_first_kind_id = fileDig.new_first_kind_id,
                    new_first_kind_name = fileDig.new_first_kind_name,
                    new_second_kind_id = fileDig.new_second_kind_id,
                    new_second_kind_name = fileDig.new_second_kind_name,
                    new_third_kind_id = fileDig.new_third_kind_id,
                    new_third_kind_name = fileDig.new_third_kind_name,

                    // 新职位信息
                    new_job_category_id = fileDig.new_major_kind_id,
                    new_job_category_name = fileDig.new_major_kind_name,
                    new_job_id = fileDig.new_major_id,
                    new_job_name = fileDig.new_major_name,

                    // 新薪酬信息
                    new_salary_standard_id = fileDig.new_salary_standard_id,
                    new_salary_standard_name = fileDig.new_salary_standard_name,
                    new_salary_total = fileDig.new_salary_sum,



                    // 其他信息
                    registrant = fileDig.register,
                    transferReason = fileDig.change_reason,

                    checkr = fileDig.checker,
                    registration_time = fileDig.regist_time,
                    check_time = fileDig.check_time,




                }
            };

            return Ok(result);

           


        }




    }
}
